package rgbos.forum.persistance;

import java.sql.DriverManager;
import java.sql.SQLException;

public class SQLUtil {
	static java.sql.Connection conn_ = null;
	public void connect(){
	    try {
	    	System.out.println("Starting to Connect...");
	        // Load the JDBC driver
	        String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver
	        Class.forName(driverName);
	        // Create a connection to the database
	        String serverName = "LOCALHOST:1212";
	        String mydatabase = "keinang";
	        String url = "jdbc:mysql://" + serverName +  "/" + mydatabase; // a JDBC url
	        String username = "keinang";
	        String password = "7qc(2]RS";
	        conn_ = DriverManager.getConnection(url, username, password);
	    } catch (ClassNotFoundException e) {
	    	System.out.println(e.getMessage());
	    	return;
	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    	return;
	    }
	    System.out.println("Connected");
	}

	public void createTables(){
		createTableUsers();
		createTablePosts();
		createTableTopics();
		createTableTopicTOPosts();
		createTableUserHour();
	}

	public void createNewTables(){
		try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql1 = "DROP TABLE topics";
	        String sql2 = "DROP TABLE posts";
	        String sql3 = "DROP TABLE users";
	        String sql4 = "DROP TABLE topicsPosts";
	        String sql5 = "DROP TABLE usersHour";
	        stmt.executeUpdate(sql1);
	        stmt.executeUpdate(sql2);
	        stmt.executeUpdate(sql3);
	        stmt.executeUpdate(sql4);
	        stmt.executeUpdate(sql5);
	        createTables();
	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void createTableUserHour() {
	   try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "CREATE TABLE usersHour("
            + "hour               INTEGER PRIMARY KEY, "// Hour Id
            + "totalUsers         INTEGER(60))";        // Total Users
	        stmt.executeUpdate(sql);
	        initTableUserHour();
	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	private void initTableUserHour() {
		try {
			java.sql.Statement st = SQLUtil.conn_.createStatement();
			for (int i=1;i<25;i++){
				st.executeUpdate("INSERT INTO usersHour (hour,totalUsers) VALUES("+i+",0)");
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}
	}

	public void createTableUsers(){
	   try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "CREATE TABLE users("
            + "username          VARCHAR(60)PRIMARY KEY, "       // UserName
            + "password          VARCHAR(60), "   			     // Password
            + "email             VARCHAR(60), "   			     // Email
            + "permGroup         VARCHAR(60), "	         		 // PermissionGroup
	        + "isOnline          INTEGER)";     		         // isOnline
	        stmt.executeUpdate(sql);

	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void addCulomn(String colName,String colType){
		try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "ALTER TABLE users ADD "+colName+" "+colType;     		         // isOnline
	        stmt.executeUpdate(sql);

	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void createTableTopics(){
	   try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "CREATE TABLE topics("
            + "id               INTEGER PRIMARY KEY AUTO_INCREMENT, "// topic ID
            + "title            VARCHAR(60), "        // title
            + "authorName       VARCHAR(60), "        // authorName
            + "creationDate     DATE,"         // date
            + "postCount 		INTEGER,"			  // post Count
            + "viewCount 		INTEGER)";			  // view Count
	        stmt.executeUpdate(sql);

	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void createTablePosts(){
	   try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "CREATE TABLE posts("
            + "id               INTEGER PRIMARY KEY AUTO_INCREMENT, "// post ID
            + "title            VARCHAR(60), "        // title
            + "authorName       VARCHAR(60), "        // authorName
            + "creationDate     TIMESTAMP,"         // date
            + "body				VARCHAR(300))";		  // body
	        stmt.executeUpdate(sql);

	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void createTableTopicTOPosts(){
	   try {
	        java.sql.Statement stmt = conn_.createStatement();
	        String sql = "CREATE TABLE topicsPosts("
            + "postId          INTEGER PRIMARY KEY, "   // topicId
            + "topicId         INTEGER )";  // postId
	        stmt.executeUpdate(sql);

	    } catch (SQLException e) {
	    	System.out.println(e.getMessage());
	    }
	}
	public void closeConnection(){
		try {
			conn_.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

